import threading
from datetime import datetime
from contextlib import contextmanager
from typing import Dict, List, Optional, Any, Union
from infrastructure.db_pool_manager import DatabasePoolManager

class EmailDao:
    """MySQL数据库的邮件数据访问对象"""
    
    def __init__(self, config_path: str = "conf/dao_config.toml"):
        self.db_manager = DatabasePoolManager(config_path)
        self.config = self.db_manager.get_config()
        self._lock = threading.Lock()
    
    def reload_config(self):
        """重新加载配置文件"""
        with self._lock:
            self.db_manager.reload_config()
            self.config = self.db_manager.get_config()
    
    @property
    def table_name(self) -> str:
        """获取表名"""
        return self.config['table']['email_processing_results']
    
    @contextmanager
    def get_connection(self):
        """获取数据库连接的上下文管理器"""
        connection = self.db_manager.get_connection()
        try:
            yield connection
        except Exception as e:
            connection.rollback()
            raise e
        finally:
            connection.close()
    
    def _format_datetime(self, dt: Union[str, datetime, None]) -> Optional[str]:
        """格式化日期时间"""
        if dt is None:
            return None
        if isinstance(dt, str):
            try:
                # 尝试解析字符串日期
                parsed_dt = datetime.fromisoformat(dt.replace('Z', '+00:00'))
                return parsed_dt.strftime('%Y-%m-%d %H:%M:%S')
            except:
                return dt
        if isinstance(dt, datetime):
            return dt.strftime('%Y-%m-%d %H:%M:%S')
        return str(dt)
    
    def save(self, email_data: Dict[str, Any]) -> bool:
        """保存邮件处理结果"""
        try:
            if not email_data.values():
                raise ValueError(f"❌ 保存邮件结果失败: 插入数据为空")

            # print(email_data)
            # 构建动态SQL（MySQL使用ON DUPLICATE KEY UPDATE）
            fields = list(email_data.keys())
            placeholders = ', '.join(['%s' for _ in fields])
            field_names = ', '.join([f'`{field}`' for field in fields])
            
            # 构建更新部分
            update_fields = [f'`{field}` = VALUES(`{field}`)' for field in fields if field != 'created_at']
            update_clause = ', '.join(update_fields)
            
            sql = f"""INSERT INTO `{self.table_name}` ({field_names}) 
                     VALUES ({placeholders})
                     ON DUPLICATE KEY UPDATE {update_clause}"""
            
            values = list(email_data.values())
            
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, values)
                    conn.commit()
                return True
                
        except Exception as e:
            raise Exception(f"❌ 保存邮件结果失败: {e}, email_data: {email_data}")
    def query(self, table_name: str = None, conditions: Dict[str, Any] = None, 
                         select_fields: List[str] = None, order_by: str = None, 
                         limit: int = None, offset: int = 0) -> List[Dict[str, Any]]:
        """通用动态查询函数"""
        try:
            if table_name is None:
                table_name = self.table_name
            
            # 构建SELECT子句
            if select_fields:
                select_clause = ', '.join([f'`{field}`' for field in select_fields])
            else:
                select_clause = '*'
            
            # 构建WHERE子句
            where_clause = ''
            params = []
            if conditions:
                where_conditions = []
                for field, value in conditions.items():
                    if value is None:
                        where_conditions.append(f'`{field}` IS NULL')
                    elif isinstance(value, (list, tuple)):
                        # IN查询
                        placeholders = ', '.join(['%s' for _ in value])
                        where_conditions.append(f'`{field}` IN ({placeholders})')
                        params.extend(value)
                    elif isinstance(value, str) and value.startswith('%') and value.endswith('%'):
                        # LIKE查询
                        where_conditions.append(f'`{field}` LIKE %s')
                        params.append(value)
                    else:
                        where_conditions.append(f'`{field}` = %s')
                        params.append(value)
                
                if where_conditions:
                    where_clause = 'WHERE ' + ' AND '.join(where_conditions)
            
            # 构建ORDER BY子句
            order_clause = ''
            if order_by:
                order_clause = f'ORDER BY {order_by}'
            
            # 构建LIMIT子句
            limit_clause = ''
            if limit is not None:
                if offset > 0:
                    limit_clause = f'LIMIT {offset}, {limit}'
                else:
                    limit_clause = f'LIMIT {limit}'
            
            # 组装完整SQL
            sql = f"SELECT {select_clause} FROM `{table_name}` {where_clause} {order_clause} {limit_clause}".strip()
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, params)
                    rows = cursor.fetchall()
                    
                    # 处理日期时间字段
                    result = []
                    for row in rows:
                        for field_name, field_config in self.config.get('fields', {}).items():
                            if field_name in row and field_config.get('format') == 'datetime' and row[field_name]:
                                if isinstance(row[field_name], datetime):
                                    row[field_name] = row[field_name].strftime('%Y-%m-%d %H:%M:%S')
                        result.append(dict(row))
                    
                    return result
                    
        except Exception as e:
            raise Exception(f"❌ 查询邮件数据失败: {e}")
    
    def count_records(self, table_name: str = None, conditions: Dict[str, Any] = None) -> int:
        """统计记录数量"""
        try:
            if table_name is None:
                table_name = self.table_name
            
            where_clause = ''
            params = []
            if conditions:
                where_conditions = []
                for field, value in conditions.items():
                    if value is None:
                        where_conditions.append(f'`{field}` IS NULL')
                    elif isinstance(value, (list, tuple)):
                        placeholders = ', '.join(['%s' for _ in value])
                        where_conditions.append(f'`{field}` IN ({placeholders})')
                        params.extend(value)
                    elif isinstance(value, str) and value.startswith('%') and value.endswith('%'):
                        where_conditions.append(f'`{field}` LIKE %s')
                        params.append(value)
                    else:
                        where_conditions.append(f'`{field}` = %s')
                        params.append(value)
                
                if where_conditions:
                    where_clause = 'WHERE ' + ' AND '.join(where_conditions)
            
            sql = f"SELECT COUNT(*) as count FROM `{table_name}` {where_clause}".strip()
            
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, params)
                    result = cursor.fetchone()
                    return result['count'] if result else 0
                    
        except Exception as e:
            raise Exception(f"❌ 统计记录数量失败: {e}")
    
    def batch_save(self, email_data_list: List[Dict[str, Any]]) -> Dict[str, Any]:
        """使用executemany进行批量保存邮件处理结果"""
        try:
            if not email_data_list:
                return {'success_count': 0, 'failed_count': 0, 'total_count': 0}
            
            # 获取所有字段名
            all_fields = set()
            for email_data in email_data_list:
                all_fields.update(email_data.keys())
            
            all_fields = sorted(list(all_fields))
            
            # 统一字段（为缺失字段填充None）
            normalized_data = []
            for data in email_data_list:
                normalized_row = []
                for field in all_fields:
                    normalized_row.append(data.get(field))
                normalized_data.append(normalized_row)
            
            # 构建SQL
            field_names = ', '.join([f'`{field}`' for field in all_fields])
            placeholders = ', '.join(['%s' for _ in all_fields])
            update_fields = [f'`{field}` = VALUES(`{field}`)' for field in all_fields if field != 'created_at']
            update_clause = ', '.join(update_fields)
            
            sql = f"""INSERT INTO `{self.table_name}` ({field_names}) 
                                VALUES ({placeholders})
                                ON DUPLICATE KEY UPDATE {update_clause}"""
            
            # 执行批量插入
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.executemany(sql, normalized_data)
                    conn.commit()
                    
                    return {
                        'success_count': len(email_data_list),
                        'failed_count': 0,
                        'total_count': len(email_data_list)
                    }
                    
        except Exception as e:
            return {
                'success_count': 0,
                'failed_count': len(email_data_list),
                'total_count': len(email_data_list),
                'error': str(e)
            }
    
    def update(self, update_data: Dict[str, Any], conditions: Dict[str, Any], 
                      table_name: str = None) -> Dict[str, Any]:
        """通用更新函数"""
        try:
            if table_name is None:
                table_name = self.table_name
                
            if not update_data:
                return {
                    'success': False,
                    'affected_rows': 0,
                    'error': '更新数据不能为空'
                }
                
            if not conditions:
                return {
                    'success': False,
                    'affected_rows': 0,
                    'error': '更新条件不能为空，为防止误操作'
                }
            
            # 构建SET子句
            set_conditions = []
            set_params = []
            for field, value in update_data.items():
                set_conditions.append(f'`{field}` = %s')
                set_params.append(value)
            
            set_clause = ', '.join(set_conditions)
            
            # 构建WHERE子句
            where_conditions = []
            where_params = []
            for field, value in conditions.items():
                if value is None:
                    where_conditions.append(f'`{field}` IS NULL')
                elif isinstance(value, (list, tuple)):
                    # IN查询
                    placeholders = ', '.join(['%s' for _ in value])
                    where_conditions.append(f'`{field}` IN ({placeholders})')
                    where_params.extend(value)
                elif isinstance(value, str) and value.startswith('%') and value.endswith('%'):
                    # LIKE查询
                    where_conditions.append(f'`{field}` LIKE %s')
                    where_params.append(value)
                else:
                    where_conditions.append(f'`{field}` = %s')
                    where_params.append(value)
            
            where_clause = ' AND '.join(where_conditions)
            
            # 组装完整SQL
            sql = f"UPDATE `{table_name}` SET {set_clause} WHERE {where_clause}"
            params = set_params + where_params
            
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, params)
                    affected_rows = cursor.rowcount
                    conn.commit()
                    
                    return {
                        'success': True,
                        'affected_rows': affected_rows,
                        'sql': sql,
                        'message': f'成功更新 {affected_rows} 条记录'
                    }
                    
        except Exception as e:
            raise Exception(f"❌ 更新邮件数据失败: {e}")
    
    def delete_by_ids(self, ids: Union[List[Any], Any], id_field: str = 'id', 
                             table_name: str = None) -> Dict[str, Any]:
        """根据ID列表删除记录"""
        try:
            if table_name is None:
                table_name = self.table_name
                
            # 统一处理为列表
            if not isinstance(ids, (list, tuple)):
                ids = [ids]
                
            if not ids:
                return {
                    'success': False,
                    'deleted_count': 0,
                    'error': 'ID列表不能为空'
                }
            
            # 过滤掉None值
            valid_ids = [id_val for id_val in ids if id_val is not None]
            if not valid_ids:
                return {
                    'success': False,
                    'deleted_count': 0,
                    'error': 'ID列表中没有有效的ID值'
                }
            
            # 构建SQL
            placeholders = ', '.join(['%s' for _ in valid_ids])
            sql = f"DELETE FROM `{table_name}` WHERE `{id_field}` IN ({placeholders})"
            
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, valid_ids)
                    deleted_count = cursor.rowcount
                    conn.commit()
                    
                    return {
                        'success': True,
                        'deleted_count': deleted_count,
                        'total_ids': len(valid_ids),
                        'sql': sql,
                        'message': f'成功删除 {deleted_count} 条记录'
                    }
                    
        except Exception as e:
            raise Exception(f"❌ 根据ID删除记录失败: {e}")
    
    def delete_by_conditions(self, conditions: Dict[str, Any], 
                                   table_name: str = None) -> Dict[str, Any]:
        """根据条件删除记录"""
        try:
            if table_name is None:
                table_name = self.table_name
                
            if not conditions:
                return {
                    'success': False,
                    'deleted_count': 0,
                    'error': '删除条件不能为空，为防止误操作'
                }
            
            # 构建WHERE子句
            where_conditions = []
            params = []
            for field, value in conditions.items():
                if value is None:
                    where_conditions.append(f'`{field}` IS NULL')
                elif isinstance(value, (list, tuple)):
                    # IN查询
                    placeholders = ', '.join(['%s' for _ in value])
                    where_conditions.append(f'`{field}` IN ({placeholders})')
                    params.extend(value)
                elif isinstance(value, str) and value.startswith('%') and value.endswith('%'):
                    # LIKE查询
                    where_conditions.append(f'`{field}` LIKE %s')
                    params.append(value)
                else:
                    where_conditions.append(f'`{field}` = %s')
                    params.append(value)
            
            where_clause = ' AND '.join(where_conditions)
            
            # 组装完整SQL
            sql = f"DELETE FROM `{table_name}` WHERE {where_clause}"
            
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, params)
                    deleted_count = cursor.rowcount
                    conn.commit()
                    
                    return {
                        'success': True,
                        'deleted_count': deleted_count,
                        'conditions': conditions,
                        'sql': sql,
                        'message': f'成功删除 {deleted_count} 条记录'
                    }
                    
        except Exception as e:
            raise Exception(f"❌ 根据条件删除记录失败: {e}")
